
CREATE TABLE [bs_customer]  (
  [cus_id] int  NOT NULL identity(1,1) ,
  [cus_code] varchar(20) NOT NULL DEFAULT '',
  [cus_name] varchar(30) NOT NULL DEFAULT '',
  [cus_fullname] varchar(100) NOT NULL DEFAULT '' ,
  [cus_email] varchar(100) NOT NULL DEFAULT '' ,
  [cus_address] varchar(200) NOT NULL DEFAULT '' ,
  [cus_tel] varchar(50) NOT NULL DEFAULT '' ,
  [cus_fax] varchar(50) NOT NULL DEFAULT '' ,
  [cus_taxcode] varchar(30) NOT NULL DEFAULT '' ,  
  [cus_ifstop] bit NOT NULL DEFAULT 0 ,
  [cus_createby] varchar(50) NOT NULL DEFAULT '' ,
  [cus_createtime] datetime NOT NULL DEFAULT GETDATE(),
  [cus_UpdatedTime] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  CONSTRAINT [PK_bs_customer] PRIMARY KEY CLUSTERED ([cus_id] ASC)
) 
GO
--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'bs_customer'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_id'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户编码', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户名称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户全称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_fullname'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户电子邮件', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_email'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户地址', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_address'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户电话', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_tel'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户传真', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_fax'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'客户税号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_taxcode'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否使用', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_ifstop'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建人', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_createby'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_createtime'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'更新时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_customer', @level2type = N'COLUMN',@level2name = N'cus_UpdatedTime'
GO



-- ----------------------------
-- Table structure for bs_provider
-- ----------------------------

CREATE TABLE [bs_provider]  (  
  [Pro_id] int  NOT NULL IDENTITY(1,1),
  [Pro_code] varchar(20) NOT NULL ,
  [Pro_name] varchar(30) NOT NULL ,
  [Pro_fullname] varchar(100) NOT NULL DEFAULT '' ,
  [Pro_email] varchar(100) NOT NULL DEFAULT '' ,
  [Pro_address] varchar(200) NOT NULL DEFAULT '' ,
  [Pro_tel] varchar(50) NOT NULL DEFAULT '' ,
  [Pro_fax] varchar(50) NOT NULL DEFAULT '' ,
  [Pro_taxcode] varchar(30) NOT NULL DEFAULT '' ,  
  [pro_ifstop] bit NOT NULL DEFAULT 0  ,
  [pro_createby] varchar(50) NOT NULL DEFAULT '' ,
  [pro_createtime] datetime NOT NULL ,
  [pro_UpdatedTime] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  CONSTRAINT [PK_bs_provider] PRIMARY KEY CLUSTERED ([Pro_id] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'bs_provider'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_id'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商编码', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商名称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商全称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_fullname'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商电子邮件', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_email'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商地址', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_address'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商电话', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_tel'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商传真', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_fax'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'供应商税号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_taxcode'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否使用', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_ifstop'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建人', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_createby'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_createtime'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'更新时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'bs_provider', @level2type = N'COLUMN',@level2name = N'pro_UpdatedTime'
GO



CREATE TABLE [dbo].[BS_MatType](
	[MTP_CODE] [varchar](20) NOT NULL,
	[MTP_Name] [varchar](50) NOT NULL,
	[MTP_ParCode] [varchar](20) NULL,
	[MTP_Property] [int] NOT NULL,
	[MTP_Remark] [varchar](200) NULL,
 CONSTRAINT [PK_BS_MatType] PRIMARY KEY CLUSTERED 
(
	[MTP_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BS_MatType] ADD  CONSTRAINT [DF_BS_MatType_MTP_TYPE]  DEFAULT ((0)) FOR [MTP_Property]
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'物料类别列', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'BS_MatType'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类别编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MatType', @level2type = N'COLUMN',@level2name = N'MTP_CODE'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类别', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MatType', @level2type = N'COLUMN',@level2name = N'MTP_Name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'父级类别编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MatType', @level2type = N'COLUMN',@level2name = N'MTP_ParCode'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'属性', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MatType', @level2type = N'COLUMN',@level2name = N'MTP_Property'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'说明', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MatType', @level2type = N'COLUMN',@level2name = N'MTP_Remark'
GO



CREATE TABLE [dbo].[BS_MATERIAL](
	[mat_code] varchar(20) NOT NULL DEFAULT '',
	[mat_name] varchar(200) NOT NULL DEFAULT '',
	[mat_spec] varchar(200) NOT NULL DEFAULT '',
	[unt_code] varchar(20) NOT NULL DEFAULT '',
	[mat_type] varchar(20) NOT NULL DEFAULT '',
	[mat_barcode] varchar(50) NOT NULL DEFAULT '',
	[mat_remark] varchar(200) NOT NULL DEFAULT '',
	[mat_py] varchar(100) NOT NULL DEFAULT '',
	[mat_ifstop] bit NOT NULL DEFAULT 0,
	[mtp_code] varchar(20) NOT NULL DEFAULT '',
	[mat_maxstock] numeric(18, 6) NOT NULL DEFAULT 0,
	[mat_minstock] numeric(18, 6) NOT NULL DEFAULT 0,
	[mat_from] varchar(1) NOT NULL DEFAULT 'M',
	[mat_property] int NOT NULL DEFAULT 0,		
	[inci_id] int NULL DEFAULT '',
 CONSTRAINT [PK_BS_MATERIAL] PRIMARY KEY CLUSTERED 
(
	[mat_code] ASC
)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字典表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'BS_MATERIAL'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'物料编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'名称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'规格', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_spec'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'单位', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'unt_code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_type'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'条码', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_barcode'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'说明', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_remark'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'助记符', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_py'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'停用', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_ifstop'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类别', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mtp_code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'最大库存', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_maxstock'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'最小库存', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_minstock'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'来源', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_from'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'属性', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'mat_property'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'INCI', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'BS_MATERIAL', @level2type = N'COLUMN',@level2name = N'inci_id'
GO

-- ----------------------------
-- Table structure for sm_dictionary
-- ----------------------------
CREATE TABLE [sm_dictionary]( 
  [DIC_ID] int  NOT NULL identity(1,1),
  [DIC_TableCode] varchar(200) NULL DEFAULT '' ,
  [DIC_Code] varchar(100) NOT NULL,
  [DIC_Name] varchar(100) NULL DEFAULT '' ,
  [Dic_DataKind] varchar(40) NULL DEFAULT '' ,
  [DIC_Display] bit NULL DEFAULT 1 ,
  [Dic_Width] int NOT NULL DEFAULT 0 ,
  [Dic_IndexNo] int  NOT NULL DEFAULT 0,
  [DIC_SelectValues] varchar(800) NULL DEFAULT '' ,
  [DIC_ifSQL] bit NOT NULL DEFAULT 0 ,
  [Dic_ifFindSql] bit NOT NULL DEFAULT 0 ,
  [Dic_FinSql] varchar(1600) NULL DEFAULT '' ,
  CONSTRAINT [PK_sm_dictionary] PRIMARY KEY CLUSTERED ([DIC_ID] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字典表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_dictionary'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字典ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'表名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_TableCode'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段说明', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_Name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'Dic_DataKind'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否显示', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_Display'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'宽度', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'Dic_Width'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'序号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'Dic_IndexNo'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'选择值', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_SelectValues'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否使用查询', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'DIC_ifSQL'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否使用查找', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'Dic_ifFindSql'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'查询语句', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_dictionary', @level2type = N'COLUMN',@level2name = N'Dic_FinSql'
GO

-- ----------------------------
-- Table structure for sm_modulecotrol
-- ----------------------------
CREATE TABLE [sm_modulecotrol]  ( 
  [MDC_ID] int NOT NULL identity(1,1) ,
  [MOD_CODE] varchar(20) NOT NULL,
  [MDC_TableName] varchar(50) NOT NULL,
  [MDC_KeyField] varchar(50) NULL DEFAULT '' ,
  [MDC_IfAutoNumber] bit NULL DEFAULT '' ,
  [MDC_NumberHead] varchar(5) NULL DEFAULT '' ,
  [MDC_NumberRule] varchar(50) NULL DEFAULT '' ,
  [MDC_KeyNo] varchar(50) NULL DEFAULT '' ,
  [MDC_Year] tinyint NULL DEFAULT '' ,
  [MDC_Month] tinyint NULL DEFAULT '' ,
  [MDC_Day] tinyint NULL DEFAULT '' ,
  [MDC_Inc] tinyint NULL DEFAULT '' ,
  [mdc_createrfield] varchar(50) NULL DEFAULT '',
  [mdc_createtimefield] varchar(50) NULL DEFAULT '',
  [MDC_CheckField] varchar(50) NULL DEFAULT '',
  [mdc_checktimefield] varchar(50) NULL DEFAULT '',
  [MDC_ApproveField] varchar(50) NULL DEFAULT '',
  [mdc_approvetimefield] varchar(50) NULL DEFAULT '',
  [MDC_AuditField] varchar(50) NULL DEFAULT '',
  [MDC_AuditTimeField] varchar(50) NULL DEFAULT '',
  [MDC_CloseField] varchar(50) NULL DEFAULT '',
  [MDC_CloseTimeField] varchar(50) NULL DEFAULT '',
  [MDC_CancelField] varchar(50) NULL DEFAULT '',
  [MDC_CancelTimeField] varchar(50) NULL DEFAULT '',
  CONSTRAINT [PK_sm_modulecotrol] PRIMARY KEY CLUSTERED ([MDC_ID] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块控制表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_modulecotrol'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块控制ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MOD_CODE'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'主表名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_TableName'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'主键字段名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_KeyField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否自动编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_IfAutoNumber'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'编号头', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_NumberHead'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'编号规则', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_NumberRule'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_KeyNo'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'年份位数', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_Year'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'月份位数', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_Month'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'日份位数', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_Day'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'流水号长度', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_Inc'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建人字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'mdc_createrfield'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建时间字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'mdc_createtimefield'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'提交人字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_CheckField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'提交时间字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'mdc_checktimefield'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'审核人字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_ApproveField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'审核时间字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'mdc_approvetimefield'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'记账人字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_AuditField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'记账时间字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_AuditTimeField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'完结人字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_CloseField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'完结时间字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_CloseTimeField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'取消人字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_CancelField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'取消时间字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulecotrol', @level2type = N'COLUMN',@level2name = N'MDC_CancelTimeField'

GO

-- ----------------------------
-- Table structure for sm_modulefields
-- ----------------------------

CREATE TABLE [sm_modulefields]  ( 
  [MDF_ID] int NOT NULL identity(1,1) ,
  [MOD_Code] varchar(20) NOT NULL,
  [MDF_QueryName] varchar(50) NOT NULL,
  [MDF_Tablename] varchar(255) NOT NULL,
  [MDF_FieldName] varchar(50) NOT NULL,
  [MDF_Displaylabel] varchar(100) NOT NULL,
  [MDF_Display] bit NOT NULL DEFAULT 1 ,
  [MDF_Width] int NOT NULL DEFAULT 1,
  [MDF_ColWidth] int NOT NULL DEFAULT 10,
  [MDF_Index] int NOT NULL DEFAULT 0 ,
  [MDF_LookupSQL] varchar(1000) NOT NULL DEFAULT '',
  [MDF_LookupField] varchar(1000) NOT NULL DEFAULT '',
  [MDF_SelectSQL] varchar(1000) NOT NULL DEFAULT '',
  [MDF_SelectField] varchar(1000) NOT NULL DEFAULT '',
  CONSTRAINT [PK_sm_modulefields] PRIMARY KEY CLUSTERED ([MDF_ID] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块字段表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_modulefields'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块字段ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MOD_CODE'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'查询类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_QueryName'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'表名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_Tablename'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_FieldName'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段中文名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_Displaylabel'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否显示', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_Display'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'显示宽度', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_Width'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'列表宽度', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_ColWidth'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'序号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_Index'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'关联语句', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_LookupSQL'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'关联字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_LookupField'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'搜索名句', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_SelectSQL'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'搜索字段', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulefields', @level2type = N'COLUMN',@level2name = N'MDF_SelectField'

GO

-- ----------------------------
-- Table structure for sm_modulemasterset
-- ----------------------------
CREATE TABLE [sm_modulemasterset]  (  
  [MMS_ID] int NOT NULL identity(1,1),
  [MOD_Code] varchar(50) NOT NULL DEFAULT '',
  [MMS_Fieldname] varchar(50) NOT NULL DEFAULT '',
  [MMS_Type] varchar(50) NOT NULL DEFAULT 'TEXT' ,
  [MMS_Index] int  NOT NULL DEFAULT 0  ,
  [MMS_CanInput] bit NOT NULL DEFAULT 1  ,
  CONSTRAINT [PK_sm_modulemasterset] PRIMARY KEY CLUSTERED ([MMS_ID] ASC)
) 
GO


--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块主表编辑字段表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_modulemasterset'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulemasterset', @level2type = N'COLUMN',@level2name = N'MMS_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulemasterset', @level2type = N'COLUMN',@level2name = N'MOD_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulemasterset', @level2type = N'COLUMN',@level2name = N'MMS_Fieldname'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'显示类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulemasterset', @level2type = N'COLUMN',@level2name = N'MMS_Type'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'字段顺序', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulemasterset', @level2type = N'COLUMN',@level2name = N'MMS_Index'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否可输入', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulemasterset', @level2type = N'COLUMN',@level2name = N'MMS_CanInput'

GO
-- ----------------------------
-- Table structure for sm_modulequery
-- ----------------------------
CREATE TABLE [sm_modulequery]  ( 
  [MQY_ID] int  NOT NULL identity(1,1) ,
  [mod_code] varchar(50) NOT NULL default '',
  [mqy_type] varchar(20) NOT NULL default '',
  [mqy_sql] varchar(4000) NOT NULL default '',
  CONSTRAINT [PK_sm_modulequery] PRIMARY KEY CLUSTERED ([MQY_ID] ASC)
)
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块查询语句表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_modulequery'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulequery', @level2type = N'COLUMN',@level2name = N'MQY_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulequery', @level2type = N'COLUMN',@level2name = N'MOD_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulequery', @level2type = N'COLUMN',@level2name = N'mqy_type'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'执行脚本', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulequery', @level2type = N'COLUMN',@level2name = N'mqy_sql'

GO
-- ----------------------------
-- Table structure for sm_modulereports
-- ----------------------------
CREATE TABLE [sm_modulereports]  ( 
  [MDR_ID] int NOT NULL identity(1,1),
  [MOD_Code] varchar(20) NOT NULL DEFAULT '',
  [MDR_CODE] varchar(20) NOT NULL DEFAULT '',
  [MDR_Name] varchar(50) NOT NULL DEFAULT '',
  [MDR_IfDefault] bit NOT NULL DEFAULT 0 ,
  [MDR_IfUsed] bit NOT NULL DEFAULT 0 ,
  [MDR_Remark] varchar(200) NULL DEFAULT ''  ,
  [MDR_Data] image NULL ,
  [MDR_Index] int NOT NULL DEFAULT 0 ,
  [MDR_CreateDate] datetime NOT NULL,
  [MDR_ModifyDate] datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  CONSTRAINT [PK_sm_modulereports] PRIMARY KEY CLUSTERED ([MDR_ID] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'报表模板表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_modulereports'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'报表ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MOD_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'报表编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_CODE'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'名称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_Name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否默认', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_IfDefault'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否使用', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_IfUsed'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'说明', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_Remark'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'报表模板数据', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_Data'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'序号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_Index'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'创建时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_CreateDate'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'修改时间', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modulereports', @level2type = N'COLUMN',@level2name = N'MDR_ModifyDate'
GO


-- ----------------------------
-- Table structure for sm_modules
-- ----------------------------

CREATE TABLE [sm_modules]  ( 
  [mod_id] int  NOT NULL identity(1,1) ,
  [mod_code] varchar(20) NOT NULL DEFAULT '',
  [mod_name] varchar(50) NOT NULL DEFAULT '',
  [mod_parcode] varchar(255) NOT NULL DEFAULT '',
  [mod_index] int NOT NULL DEFAULT 0 ,
  [mod_ifstop] bit NOT NULL DEFAULT 0  ,
  [mod_classtype] int  NOT NULL DEFAULT 0 ,
  CONSTRAINT [PK_sm_modules] PRIMARY KEY CLUSTERED ([mod_id] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_modules'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'mod_id'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'MOD_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块名称', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'mod_name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'父级模块编号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'mod_parcode'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'序号', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'mod_index'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否停用', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'mod_ifstop'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'类的类型', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_modules', @level2type = N'COLUMN',@level2name = N'mod_classtype'

GO

-- ----------------------------
-- Table structure for sm_userpower
-- ----------------------------

CREATE TABLE [sm_userpower]  (
  [id] int  NOT NULL identity(1,1)  ,
  [use_id] int NOT NULL,
  [mod_id] int NOT NULL ,
  CONSTRAINT [PK_sm_userpower] PRIMARY KEY CLUSTERED ([ID] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'用户权限表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_userpower'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_userpower', @level2type = N'COLUMN',@level2name = N'ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'用户ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_userpower', @level2type = N'COLUMN',@level2name = N'use_id'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'模块ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_userpower', @level2type = N'COLUMN',@level2name = N'mod_id'

GO

-- ----------------------------
-- Table structure for sm_users
-- ----------------------------
CREATE TABLE [sm_users]  ( 
  [Use_ID] int  NOT NULL identity(1,1),
  [Use_Code] varchar(20) NOT NULL DEFAULT '',
  [Use_Name] varchar(50) NOT NULL DEFAULT '',
  [Use_phone] varchar(20) NOT NULL DEFAULT '',
  [Use_Email] varchar(50) NOT NULL DEFAULT '',
  [Use_password] varbinary(50) NOT NULL ,
  [Use_IfStop] bit NOT NULL DEFAULT 0 ,
  [Use_ifmanage] bit NOT NULL DEFAULT 0 ,
  CONSTRAINT [PK_sm_users] PRIMARY KEY CLUSTERED ([Use_ID] ASC)
) 
GO

--数据表添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'用户表', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE',@level1name = N'sm_users'
GO

--数据字段添加注释的方法分享
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'用户ID', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_ID'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'用户编码', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_Code'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'用户名', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_Name'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'电话', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_phone'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'电子邮件', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_Email'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'密码', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_password'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否停用', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_IfStop'
EXEC sys.sp_addextendedproperty @name = N'MS_Description',@value = N'是否为管理员', @level0type = N'SCHEMA',@level0name = N'dbo', @level1type = N'TABLE', @level1name = N'sm_users', @level2type = N'COLUMN',@level2name = N'Use_ifmanage'
GO


create view dbo.readdescription
as
SELECT A.name AS table_name,D.value AS table_description,
 B.name AS column_name,
 C.value AS column_description
 FROM sys.tables A
 INNER JOIN sys.columns B ON B.object_id = A.object_id
 LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
left join sys.extended_properties D on a.object_id = d.major_id AND d.minor_id = 0
 WHERE A.name = '表名'

go



-- ----------------------------
-- Procedure structure for usp_createmodulefield
-- ----------------------------

CREATE PROCEDURE [usp_createmodulefield](@modcode varchar(20),@tablename varchar(20),@type varchar(20))
as
begin

insert into sm_modulefields(MOD_Code,MDF_QueryName,MDF_TableName,MDF_FieldName,MDF_Displaylabel,MDF_Display,MDF_Width,MDF_ColWidth,MDF_Index)
SELECT  @modcode,@type, TABLE_NAME,COLUMN_NAME, column_comment,1, COALESCE(CHARACTER_MAXIMUM_LENGTH,10) as MDF_Width,COALESCE(CHARACTER_MAXIMUM_LENGTH,10) as MDF_ColWidth, ORDINAL_POSITION
FROM information_schema.COLUMNS ,shyun.enterprise
where TABLE_NAME=@tablename 
and not exists(select 1 from sm_modulefields where sm_modulefields.MOD_Code=@modcode and MDF_QueryName= @type and COLUMN_NAME=MDF_FieldName );

UPDATE sm_modulefields
SET MDF_Display = 0
WHERE MDF_FieldName LIKE '%_id';


UPDATE sm_modulefields,information_schema.COLUMNS set MDF_Displaylabel=column_comment ,MDF_Index=ORDINAL_POSITION
where TABLE_NAME = MDF_TableName AND COLUMN_NAME = MDF_FieldName and MDF_Displaylabel<>column_comment; 

UPDATE sm_modulefields,information_schema.COLUMNS set MDF_Index=ORDINAL_POSITION
where TABLE_NAME = MDF_TableName AND COLUMN_NAME = MDF_FieldName and MDF_Index<>ORDINAL_POSITION; 

insert into sm_modulemasterset(MOD_Code,MMS_Fieldname,MMS_Type,MMS_Index)
select mod_code,mdf_fieldname,'TEXT' ,MDF_Index
from sm_modulefields where MDF_QueryName=@type and sm_modulefields.mod_code=@modcode 
and not exists(select 1 from sm_modulemasterset where MDF_QueryName=@type and sm_modulefields.mod_code=@modcode and MMS_Fieldname=mdf_fieldname);

end

-- ----------------------------
-- Procedure structure for Usp_CreateNo
-- ----------------------------

CREATE PROCEDURE [dbo].[Usp_CreateNo](@V_ModuleName NVARCHAR(50),	@V_ParCode NVARCHAR(10) , @V_NewCode VARCHAR(50) OUTPUT)
as
begin
DECLARE @V_MDC_TABLEName VARCHAR(50), -- --表名
	@V_MDC_KeyField VARCHAR(50), -- 编号字段
	@V_MDC_KeyNo VARCHAR(50), -- 过去最后一个编号
	@V_MDC_NumberRule VARCHAR(50), -- 级别规则(项目编号规则)
	@V_MDC_NumberHead VARCHAR(20), -- 编码头
	@V_MDC_Type INT,-- 编号类别,0以编号规则来编号,1以编号级别来编号
	@V_MDC_Year INT,
	@V_MDC_Month INT,
	@V_MDC_Day INT,
	@V_MDC_Inc INT,
	@V_TotalLen int,
	@V_year VARCHAR(4),
	@V_month varchar(2),
	@V_day VARCHAR(2);

	IF EXISTS ( SELECT 1 FROM SM_ModuleCotrol WHERE MOD_Code = @V_ModuleName ) 
		SELECT   @V_MDC_TABLEName=ISNULL( MDC_TABLEName, '' ),	@V_MDC_KeyField=ISNULL( MDC_KeyField, '' ),	
		@V_MDC_KeyNo=ISNULL( MDC_KeyNo, '' ),	@V_MDC_NumberHead=ISNULL( MDC_NumberHead, '' ),	
		@V_MDC_NumberRule=ISNULL( MDC_NumberRule, 0 ),@V_MDC_Year=ISNULL( MDC_Year, 4 ),@V_MDC_Month=ISNULL( MDC_Month, 2 ),@V_MDC_Day=ISNULL( MDC_Day, 2 ), @V_MDC_Inc=ISNULL( MDC_Inc, 4 ) 			
		FROM
			SM_ModuleCotrol 
		WHERE
			 MOD_Code = @V_ModuleName ;		
	
	
 set @V_year=year(getdate());
 if (@V_MDC_Year)=2 	 
	 SET @V_year = right(year(getdate()),2);

 set @V_month=right('00'+MONTH(getdate()), 2);
 set @V_day=right('00'+Day(getdate()), 2);

 SET @V_TotalLen = len(@V_MDC_NumberHead) + @V_MDC_Year + @V_MDC_Month + @V_MDC_Day + @V_MDC_Inc;
 
		
		declare @V_GetNoSql varchar(8000),@V_MaxNo varchar(30)

	SET @V_GetNoSql=CONCAT('  SELECT @V_MaxNo=ISNULL(MAX(' ,@V_MDC_KeyField,'),'''')   FROM ' , @V_MDC_TABLEName,'  WHERE substring(',@V_MDC_KeyField,',1,len(''',@V_MDC_NumberHead,'''))=''',@V_MDC_NumberHead,''' AND substring(',@V_MDC_KeyField,',len(''',@V_MDC_NumberHead,''')+1,', @V_MDC_Year+ @V_MDC_Month+ @V_MDC_Day ,')=''',CONCAT(@V_year,@V_month,@V_day) ,'''
 AND len(',@V_MDC_KeyField,')=' , @V_TotalLen ,'''');		 
	
 
  EXECUTE @V_GetNoSql
	
  if ISNULL(@V_Maxno,'')='' 
	 set @V_MaxNO=CONCAT(@V_MDC_NumberHead,@V_year,@V_Month,@V_day,right(POWER(10,@V_MDC_Inc)+1,@V_MDC_Inc)) 
  else
	 set @V_MaxNO= CONCAT(@V_MDC_NumberHead,@V_year,@V_Month,@V_day ,right(CONCAT(POWER(10,@V_MDC_Inc), right(@V_MaxNo,@V_MDC_Inc )+1),@V_MDC_Inc))

	set @V_NewCode=@V_maxno;
	
  UPDATE SM_ModuleCotrol SET MDC_KeyNo=@V_NewCode WHERE  MOD_Code=@V_ModuleName;
	
end

GO